#!/usr/bin/python
#encoding=utf-8

import copy
import logging
import time
import MySQLdb
import MySQLdb.constants
import MySQLdb.converters
from DBUtils import PooledDB
from wm.etc.config import MYSQL

class TransactionManager(object):
    '''
    handles all transaction and db query
    '''
    def __init__(self, host=MYSQL['HOST'], database=MYSQL['DB'], user=MYSQL['USER'], 
                 password=MYSQL['PASSWD'],max_idle_time=7*3600):
        self.host = host
        self.database = database
        self.max_idle_time = max_idle_time

        args = dict(conv=CONVERSIONS, use_unicode=True, charset="utf8",
                    db=database)
        if user is not None:
            args["user"] = user
        if password is not None:
            args["passwd"] = password

        # We accept a path to a MySQL socket file or a host(:port) string
        if "/" in host:
            args["unix_socket"] = host
        else:
            self.socket = None
            pair = host.split(":")
            if len(pair) == 2:
                args["host"] = pair[0]
                args["port"] = int(pair[1])
            else:
                args["host"] = host
                args["port"] = 3306

        self._db = None
        self._db_args = args
        self._last_use_time = time.time()
        try:
            self.reconnect()
        except:
            logging.error("Cannot connect to MySQL on %s", self.host,
                          exc_info=True)
            
    def __del__(self):
        self.close()
     
    def startTransaction(self):
        pass
    
    def endTransaction(self):
        pass
    
    def commitTransaction(self):
        '''
        close cursor, commit current transaction 
        '''
        self._cursor.close()
        self._db.commit()
    
    def rollbackTransaction(self):
        '''
        rollback current transaction
        '''
        self._cursor.close()
        self._db.rollback()
     
    def select(self,tablename,condition=None,field=None):
        '''
                    获取指写数据库指定字段的值
        @tablename 表名或者是SQL语句，表名不能以select开头
        @condition 条件，默认为空
        @field 字段名,默认为空代表*号
        return 元组格式的数据
        '''
        if tablename[0:6].upper() == 'SELECT':
            sql = tablename
        else:
            if field:
                sql = "SELECT "+field+ " FROM `"+tablename+"`"
            else:
                sql = "SELECT * FROM `"+tablename+"`"
        
            if condition:
                sql = sql + " WHERE " + condition
        
        try:
            self._cursor.execute(sql)
            return self._cursor.fetchall()
        except Exception as e:
            logging.error("Fail to execute %s", tablename)
            raise
    
    def execute(self,sql):
        try:
            self._cursor.execute(sql)
        except OperationalError,IntegrityError:
            logging.error("Error connecting to MySQL on %s", self.host)
            raise
        
    def update(self,sql):
        self.execute(sql)

    def close(self):
        """Closes this database connection."""
        if getattr(self, "_db", None) is not None:
            self._db.close()
            self._db = None

    def reconnect(self):
        """Closes the existing database connection and re-opens it."""
        self.close()
        try:
            self._db = MySQLdb.connect(**self._db_args)  
        except:
            #if failed,we try pool connnection
            pooldb = PooledDB.PooledDB(MySQLdb, maxusage=MYSQL['SIZE'],**self._db_args) 
            self._db = pooldb.connection()
        self._cursor =self._db.cursor()
        
    def cursor(self):
        '''
        create con cursor
        '''
        '''self._ensure_connected()'''
        self._cursor =self._db.cursor()  
         
    def _ensure_connected(self):
        '''
        Mysql by default closes client connections that are idle for
        8 hours, but the client library does not report this fact until
        you try to perform a query and it fails.  Protect against this
        case by preemptively closing and reopening the connection
        if it has been idle for too long (7 hours by default).
        '''
        if (self._db is None or
            (time.time() - self._last_use_time > self.max_idle_time)):
            self.reconnect()
        self._last_use_time = time.time()
    
    def sava_db_log(self,logstr):
        try:
            sql = ""
            self._ensure_connected()
            self.execute(sql)
            self.commitTransaction()
        except:
            self.rollbackTransaction()
        finally:
            self.close()
        


   
# Fix the access conversions to properly recognize unicode/binary
FIELD_TYPE = MySQLdb.constants.FIELD_TYPE
FLAG = MySQLdb.constants.FLAG
CONVERSIONS = copy.deepcopy(MySQLdb.converters.conversions)

field_types = [FIELD_TYPE.BLOB, FIELD_TYPE.STRING, FIELD_TYPE.VAR_STRING]
if 'VARCHAR' in vars(FIELD_TYPE):
    field_types.append(FIELD_TYPE.VARCHAR)

for field_type in field_types:
    CONVERSIONS[field_type].insert(0, (FLAG.BINARY, str))


# Alias some common MySQL exceptions
IntegrityError = MySQLdb.IntegrityError
OperationalError = MySQLdb.OperationalError
        